﻿<%@ WebHandler Language="C#" Class="SIPO.Web.AjaxHandler.Transaction.recDemandhandler" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

using SIPO.Library.DataAccess.Transaction;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DataAccess.Administration;
using SIPO.Library.DomainObject.Administration;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DataAccess.Administration.UserManagement;

using System.Net.Mail;
using System.Net;
using System.Web.SessionState;

using SIPO.Library.Enum;
using SIPO.Web.Support;

using Newtonsoft.Json.Linq;

namespace SIPO.Web.AjaxHandler.Transaction
{
    public class recDemandhandler : SIPO.Web.BaseHandler
    {
        recDemandRepo recDemandDal = new recDemandRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        recDemandDetailRepo recDemandDetDal = new recDemandDetailRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recLogRepo logDal = new recLogRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        recDemand recDemand = new recDemand();
        private string MethodName = string.Empty;
        
        public override void ProcessRequest(HttpContext context)
        {
            base.ProcessRequest(context);

            MethodName = context.Request.Params["method"];

            context.Response.Clear();
            context.Response.Buffer = true;
            
            switch (MethodName.ToLower())
            {
                case ("update"):
                    context.Response.Write(Update(context));
                    break;
                case ("insert"):
                    context.Response.Write(Insert(context));
                    break;
                case ("getdata"):
                    context.Response.Write(GetData(context));
                    break;
                case ("getdatamonitoringupayapenagihan"):
                    context.Response.Write(GetDataMonitoringUpayaPenagihan(context));
                    break;
                case ("getdatadetailmonitoringupayapenagihan"):
                    context.Response.Write(GetDataDetailMonitoringUpayaPenagihan(context));
                    break;
                case ("getdatadebiturjatuhtempo"):
                    context.Response.Write(GetDataDebiturJatuhTempo(context));
                    break;
                default:
                    // do nothing
                    break;
            }
        }

        public string Update(HttpContext context)
        {
            JSONHelper helper = new JSONHelper();
            recUserRepo recUserDal = new recUserRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
            recUser user = new recUser();
            string email = string.Empty;
            int emailIsValid = 0;
            string statusName = string.Empty;
            int statusId = 0;
            
            try
            {    
                var data = context.Request.Form;
                var demandNo = data["hfDemandNo"] == null ? string.Empty : data["hfDemandNo"].ToString();
                var letterDate = data["txtTglSurat"] == null ? string.Empty : context.Server.UrlDecode(data["txtTglSurat"]);
                var letterNo = data["txtNoSurat"] == null ? string.Empty : context.Server.UrlDecode(data["txtNoSurat"].ToString());
                var transTypeID = data["hfTransTypeID"] == null ? string.Empty : data["hfTransTypeID"].ToString();
                var transName = data["hfTransName"] == null ? string.Empty : context.Server.UrlDecode(data["hfTransName"].ToString());

                if (data["ddlUpayaPenagihan"] == null)
                {
                    statusId = data["hfStatusId"] == null ? 0 : int.Parse(data["hfStatusId"].ToString());    
                }
                else
                {
                    statusId = data["ddlUpayaPenagihan"] == null ? 0 : int.Parse(data["ddlUpayaPenagihan"].ToString());
                }

                statusName = recDemandDal.GetActionDemandById(statusId);
                
                //statusName = data["hfUpayaPenagihanName"] == null ? string.Empty : context.Server.UrlDecode(data["hfUpayaPenagihanName"]);
                var dueDateLetter = data["txtTglAkhirPeneguran"] == null ? string.Empty : context.Server.UrlDecode(data["txtTglAkhirPeneguran"]);
                email = data["hfEmail"] == null ? string.Empty : context.Server.UrlDecode(data["hfEmail"]);

                recDemand obj = new recDemand(true);
                obj.DemandNo = int.Parse(demandNo);
                obj.recDemandDetail.LatterDate = letterDate == null ? (DateTime?)null : Helper.FormatDate(letterDate, Helper.Culture.Indonesian);
                obj.recDemandDetail.LatterNo = letterNo;
                obj.recDemandDetail.StatusID = statusId;
                obj.recDemandDetail.Status = statusName;
                obj.recDemandDetail.DueDateLetter = dueDateLetter.Equals("") ? (DateTime?)null : Helper.FormatDate(dueDateLetter, Helper.Culture.Indonesian);
                obj.recDemandDetail.TransTypeID = int.Parse(transTypeID);
                obj.recDemandDetail.TransName = transName;
                obj.LastAction = statusName;
                
                //cek email
                emailIsValid = recUserDal.validasiEmail(email);

                //jika ada file untuk diupaload
                if (context.Request.Files.Count > 0)
                {
                    HttpPostedFile hpf = context.Request.Files[0] as HttpPostedFile;
                    string fileName = data["name"]; // --> name taken from PLUPLOAD!
                   
                    byte[] datas = new byte[hpf.ContentLength];
                    hpf.InputStream.Position = 0;
                    hpf.InputStream.Read(datas, 0, hpf.ContentLength);

                    obj.recDemandDetail.AttachFile = datas;
                    obj.recDemandDetail.AttachFileName = fileName;
                    hpf.InputStream.Close();
                    hpf.InputStream.Dispose();
                }

                obj.UpdatedDate = DateTime.Now;
                obj.UpdatedBy = base.UserLoggedIn;

                recDemandDal.UpdateUpayaPenagihan(obj);

                helper.IsSuccess = true;
                helper.Message = "berhasil disimpan !";
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Update";
                log.LogMessage = helper.Message;
                log.LogSource = "recDemandhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            //mengirim email ke debitur
            try
            {
                if (emailIsValid > 0 && helper.IsSuccess == true)
                {
                    SIPO.Web.Support.Membership membership = new SIPO.Web.Support.Membership(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);

                    MailMessage mail = new MailMessage();
                    MailAddress mailFrom = new MailAddress("no_reply@ojk.go.id", "OJK");
                    mail.From = mailFrom;
                    MailAddress mailTo = new MailAddress(email);
                    mail.To.Add(mailTo);
                    mail.Subject = "Upaya Penagihan OJK";
                    mail.Body = "sampai saat ini anda belum memenuhi kewajiban anda, sebagai tindak lanjut kami melakukan upaya penagihan berupa " + statusName + " terhadap kelalaian anda";
                    SmtpClient smtpEmail = new SmtpClient("mail.ojk.go.id");
                    smtpEmail.EnableSsl = true;
                    smtpEmail.Credentials = new NetworkCredential("no_reply@ojk.go.id", "P4ssword");
                    smtpEmail.Send(mail);

                    helper.Message = "pesan teguran telah terkirim ke debitur yang bersangkutan !";

                }
            }
            catch (Exception)
            {
            //do nothing
            }
            
            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        public string Insert(HttpContext context)
        {
            JSONHelper helper = new JSONHelper();
            recUserRepo recUserDal = new recUserRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
            recUser user = new recUser();
            string email = string.Empty;
            int emailIsValid = 0;
            string statusName = string.Empty;

            try
            {
                var data = context.Request.Form;
                var demandNo = data["hfDemandNo"] == null ? 0 : int.Parse(data["hfDemandNo"].ToString());
                var letterDate = data["txtTglSurat"] == null ? string.Empty : context.Server.UrlDecode(data["txtTglSurat"]);
                var letterNo = data["txtNoSurat"] == null ? string.Empty : context.Server.UrlDecode(data["txtNoSurat"].ToString());
                var transTypeID = data["hfTransTypeID"] == null ? string.Empty : data["hfTransTypeID"].ToString();
                var transName = data["hfTransName"] == null ? string.Empty : context.Server.UrlDecode(data["hfTransName"].ToString());
                int statusId = data["ddlUpayaPenagihan"] == null ? 0 : int.Parse(data["ddlUpayaPenagihan"].ToString());
                //statusName = data["hfUpayaPenagihanName"] == null ? string.Empty : context.Server.UrlDecode(data["hfUpayaPenagihanName"]);
                statusName = recDemandDal.GetActionDemandById(statusId);
                var dueDateLetter = data["txtTglAkhirPeneguran"] == null ? string.Empty : context.Server.UrlDecode(data["txtTglAkhirPeneguran"]);
                email = data["hfEmail"] == null ? string.Empty : context.Server.UrlDecode(data["hfEmail"]);

                recDemandDetail obj = new recDemandDetail();
                obj.DemandNo = demandNo;
                obj.LatterDate = letterDate == null ? (DateTime?)null : Helper.FormatDate(letterDate, Helper.Culture.Indonesian);
                obj.LatterNo = letterNo;
                obj.StatusID = statusId;
                obj.Status = statusName;
                obj.DueDateLetter = dueDateLetter.Equals("") ? (DateTime?)null : Helper.FormatDate(dueDateLetter, Helper.Culture.Indonesian);
                obj.TransTypeID = int.Parse(transTypeID);
                obj.TransName = transName;
                obj.Status = statusName;

                //cek email
                emailIsValid = recUserDal.validasiEmail(email);

                //jika ada file untuk diupaload
                if (context.Request.Files.Count > 0)
                {
                    HttpPostedFile hpf = context.Request.Files[0] as HttpPostedFile;
                    string fileName = data["name"]; // --> name taken from PLUPLOAD!
                    
                    byte[] datas = new byte[hpf.ContentLength];
                    hpf.InputStream.Position = 0;
                    hpf.InputStream.Read(datas, 0, hpf.ContentLength);

                    obj.AttachFileName = fileName;
                    obj.AttachFile = datas;
                    hpf.InputStream.Close();
                    hpf.InputStream.Dispose();
                }

                obj.CreatedDate = DateTime.Now;
                obj.CreatedBy = base.UserLoggedIn;

                //lakukan insert recDemandDetail
                recDemandDal.insertUpayaPenagihan(obj);

                helper.IsSuccess = true;
                helper.Message = "berhasil di simpan !";
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Insert";
                log.LogMessage = helper.Message;
                log.LogSource = "recDemandhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);

            }

            //mengirim email ke debitur
            try
            {
                if (emailIsValid > 0 && helper.IsSuccess == true)
                {
                    SIPO.Web.Support.Membership membership = new SIPO.Web.Support.Membership(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);

                    MailMessage mail = new MailMessage();
                    MailAddress mailFrom = new MailAddress("no_reply@ojk.go.id", "OJK");
                    mail.From = mailFrom;
                    MailAddress mailTo = new MailAddress(email);
                    mail.To.Add(mailTo);
                    mail.Subject = "Upaya Penagihan OJK";
                    mail.Body = "sampai saat ini anda belum memenuhi kewajiban anda, sebagai tindak lanjut kami melakukan upaya penagihan berupa " + statusName + " terhadap kelalaian anda";
                    SmtpClient smtpEmail = new SmtpClient("mail.ojk.go.id");
                    smtpEmail.EnableSsl = true;
                    smtpEmail.Credentials = new NetworkCredential("no_reply@ojk.go.id", "P4ssword");
                    smtpEmail.Send(mail);

                    helper.Message = "pesan teguran telah terkirim ke debitur yang bersangkutan !";

                }
            }
            catch (Exception)
            {
                //do nothing
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);
        }

        public string GetData(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            parameters += " and recDemandDetail.DemandDetailNo in (select * from dbo.fn_maxListUpayaPenagihan()) ";
            parameters += " and recTransactionType.TransTypeID not in(1,4,6,7,9,10,11,12,16,17,22,23,27) ";
            parameters += " and recDemand.IsActive = 1 ";
            parameters += " and (convert(date,GETDATE()) <= recDemandDetail.DueDateLetter";
            parameters += " or recDemandDetail.LatterNo is null ";
            parameters += " or recDemandDetail.DueDateLetter is null)";
            
            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recDemand.Fields.DemandNo : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recDemand> pagedList = null;

            try
            {
                //insert recDemand
                recDemandDal.insertRecDemand(base.UserLoggedIn);
                
                //tampilkan data recDemand
                totalCount = Convert.ToInt32(recDemandDal.SelectScalar(SQL.Function.Aggregate.Count, "recDemand.DemandNo", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recDemandDal.GetList(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetData";
                log.LogMessage = ex.Message;
                log.LogSource = "recDemandhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }
            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,

            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);
        }

        public string GetDataMonitoringUpayaPenagihan(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            int debtorId = context.Request["DebtorID"].Equals(string.Empty) ? 0 : int.Parse(context.Request["DebtorID"].ToString());

            if (debtorId != 0)
            {
                if (parameters.Equals(string.Empty))
                {
                    parameters += " AND recDemand.DebtorID = " + debtorId;
                }
      
            }

            parameters += " AND recDemand.LastAction is not null";
            
            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recDemand.Fields.DebtorID : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recDemand> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recDemandDal.SelectScalarMonitoringPenagihan(SQL.Function.Aggregate.Count, "recDebtor.DebtorCode", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recDemandDal.GetListMonitoringUpayaPenagihan(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataMonitoringUpayaPenagihan";
                log.LogMessage = ex.Message;
                log.LogSource = "recDemandhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }
            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,

            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);
        }

        public string GetDataDebiturJatuhTempo(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            //input parameter duedate di bawah
            DateTime? periode = context.Request.Params["Bulan"] == null ? (DateTime?)null : Helper.FormatDate(context.Request["Bulan"].ToString(), Helper.Culture.Indonesian);;
            DateTime? periodeAwal = context.Request.Params["PeriodeAwal"] == null ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAwal"].ToString(), Helper.Culture.Indonesian);
            DateTime? periodeAkhir = context.Request.Params["PeriodeAkhir"] == null ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAkhir"].ToString(), Helper.Culture.Indonesian);

            //konversi tanggal
            string sPeriode = periode == null ? string.Empty : string.Format("{0:yyyyMMdd}", periode);
            string sPeriodeAwal = periodeAwal == null ? string.Empty : string.Format("{0:yyyyMMdd}", periodeAwal);
            string sPeriodeAkhir = periodeAkhir == null ? string.Empty : string.Format("{0:yyyyMMdd}", periodeAkhir);
            
            if (!sPeriode.Equals(string.Empty))
            {
                parameters += " AND DueDate <= '" + sPeriode + "'";
            }

            if (!sPeriodeAwal.Equals(string.Empty) && !sPeriodeAkhir.Equals(string.Empty))
            {
                parameters += " AND DueDate between '" + sPeriodeAwal + "' AND '" + sPeriodeAkhir + "'";
            }

            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recBilling.Fields.DueDate : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recBilling> pagedList = null;

            try
            {
                if(!parameters.Equals(string.Empty))
                {
                totalCount = Convert.ToInt32(recDemandDal.SelectScalarMonitoring(SQL.Function.Aggregate.Count, "recDebtor.DebtorName", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recDemandDal.GetListDebiturJatuhTempo(pageSize, page, sidx, sord, parameters);
                }
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataMonitoringUpayaPenagihan";
                log.LogMessage = ex.Message;
                log.LogSource = "recDemandhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }
            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,

            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);
        }

        public string GetDataDetailMonitoringUpayaPenagihan(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);
            
            
            int demandNo = context.Request["id"] == null ? 0 : int.Parse(context.Request["id"].ToString());

            if (demandNo > 0)
            {
                parameters = " AND recDemand.DemandNo = " + demandNo + " And recDemandDetail.LatterNo is not null";
            }

            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recDemandDetail.Fields.TransName : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recDemand> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recDemandDal.SelectScalarMonitoringPenagihan(SQL.Function.Aggregate.Count, "recDemandDetail.TransName", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recDemandDal.GetListDetailMonitoringUpayaPenagihan(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataDetailMonitoringUpayaPenagihan";
                log.LogMessage = ex.Message;
                log.LogSource = "recDemandhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);
        }

        public override bool IsReusable
        {
            get
            {
                return false;
            }
        }

    }
}